package com.zhonggong.network.emp.dao.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.zhonggong.network.emp.dao.EmpDao;
import com.zhonggong.network.emp.model.Emp;


public class EmpDaoImpl implements EmpDao{
	
	Connection conn;
	Statement st;
	ResultSet rs;
	private String driver;
	private String url;
	private String user;
	private String pass;
	private Logger logger = LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);

	public EmpDaoImpl() {
		try {
			this.initParam("mysql.ini");
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pass);
			st = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void initParam(String paramfile) {
		try {
			Properties props = new Properties();
			props.load(new FileInputStream(paramfile));
			driver = props.getProperty("driver");
			url = props.getProperty("url");
			user = props.getProperty("user");
			pass = props.getProperty("pass");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private int useSql(String sql, Emp emp) {
		int result = 0;
		try {
			result = st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public void create(Emp emp) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO E_EMP( ID , NAME , GONGZI , BUMEN ) VALUES(" + null + ",'"
				+ emp.getName() + "','" + emp.getGongzi() + "','" + emp.getBumen() + "')";
		logger.debug(sql);
		logger.debug('\n');
		useSql(sql, emp);
	}

	public int remove(Emp emp) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM E_EMP WHERE ID = " + emp.getId();
		logger.debug(sql);
		logger.debug('\n');
		return useSql(sql, emp);
	}

	public void update(Emp emp) {
		// TODO Auto-generated method stub
		String sql = "UPDATE E_EMP SET NAME = '" + emp.getName() + "' , GONGZI = '" + emp.getGongzi()
		+ "' , BUMEN = '" + emp.getBumen() + "' WHERE ID = " + emp.getId();
		logger.debug(sql);
		logger.debug('\n');
		useSql(sql, emp);
	}

	public List<Emp> findByName(String name) {
		// TODO Auto-generated method stub
		List<Emp> empList = new ArrayList<Emp>();
		try {
			String sql = "SELECT ID , NAME , GONGZI , BUMEN FROM E_EMP ";
			logger.debug(sql);
			logger.debug('\n');
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setId(rs.getLong("ID"));
				emp.setName(rs.getString("NAME"));
				emp.setGongzi(rs.getString("GONGZI"));
				emp.setBumen(rs.getString("BUMEN"));
				if (emp.getName().equals(name)) {
					logger.debug("found emp:" + emp + "by name\n");
					empList.add(emp);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return empList;
	}

	public Emp findById(Long id) {
		// TODO Auto-generated method stub
		Emp emp = null;
		try {
			String selectSQL = "SELECT ID , NAME , GONGZI , BUMEN FROM E_EMP WHERE ID = " + id;
			logger.debug(selectSQL);
			logger.debug('\n');
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				emp = new Emp();
				emp.setId(rs.getLong("ID"));
				emp.setName(rs.getString("NAME"));
				emp.setGongzi(rs.getString("GONGZI"));
				emp.setBumen(rs.getString("BUMEN"));
				if (emp.getId().equals(id)) {
					logger.info("found emp:" + emp + "by id\n");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emp;
	}
}
